IF COL_LENGTH('[DW].[dMember]', 'AgeBanding') IS NULL
BEGIN
    ALTER TABLE [DW].[dMember]
    ADD [AgeBanding] varchar(10)
END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





ALTER PROCEDURE [dbo].[v9dimMergedMember] 
@Member [dbo].[MemberType] READONLY

AS 
BEGIN

WITH tMaxRowIDMembershipCard
AS
(SELECT
		sMSC.[CompanyPrefix]
	   ,sMSC.[Card No_] AS CardNo
	   ,sMSC.[Contact No_] AS ContactNo
	   ,MAX(sMSC.[bigint_timestamp]) AS RowID
	FROM [Stg$Membership Card] sMSC
	GROUP BY sMSC.[CompanyPrefix]
			,sMSC.[Card No_]
			,sMSC.[Contact No_]),

tMaxRowIDMemberContact
AS
(SELECT
		sMCT.[CompanyPrefix]
	   ,sMCT.[Contact No_]
	   ,MAX(sMCT.[bigint_timestamp]) AS [RowID]
	FROM [Stg$Member Contact] sMCT
	GROUP BY sMCT.[CompanyPrefix]
			,sMCT.[Contact No_]),

tMaxRowIDMemberClub
AS
(SELECT
		sMCL.[CompanyPrefix]
	   ,sMCL.[Code]
	   ,MAX(sMCL.[bigint_timestamp]) AS [RowID]
	FROM [stg$Member Club] sMCL
	GROUP BY sMCL.[CompanyPrefix]
			,sMCL.[Code]),

tMaxRowIDMemberScheme
AS
(SELECT
		sMES.[CompanyPrefix]
	   ,sMES.[Code]
	   ,MAX(sMES.[bigint_timestamp]) AS [RowID]
	FROM [stg$Member Scheme] sMES
	GROUP BY sMES.[CompanyPrefix]
			,sMES.[Code]),

tMembershipCard
AS
(SELECT
		sMSC.[CompanyPrefix]
	   ,sMSC.[Contact No_]
	   ,sMSC.[Account No_]
	   ,sMSC.[Status]
	   ,sMSC.[Card No_]
	   ,sMSC.[First Date Used]
	   ,sMSC.[Club Code]
	   ,sMSC.[Scheme Code]
	   ,sMSC.[bigint_timestamp] AS [RowID]
	FROM [stg$Membership Card] sMSC
	RIGHT JOIN tMaxRowIDMembershipCard tMRC
		ON sMSC.[CompanyPrefix] = tMRC.[CompanyPrefix]
		AND sMSC.[bigint_timestamp] = tMRC.[RowID]
		AND sMSC.[Card No_] = tMRC.[CardNo]
		AND sMSC.[Contact No_] = tMRC.[ContactNo]),

tMemberContact
AS
(SELECT
		sMEC.[CompanyPrefix]
	   ,sMEC.[Account No_]
	   ,sMEC.[Contact No_]
	   ,sMEC.[Name] AS [Fullname]
	   ,sMEC.[First Name] AS [FirstName]
	   ,sMEC.[Middle Name] AS [MiddleName]
	   ,sMEC.[Surname]
	   ,sMEC.[Address]
	   ,sMEC.[Address 2] AS [Address2]
	   ,sMEC.[City]
	   ,sMEC.[Post Code] AS [PostCode]
	   ,sMEC.[County]
	   ,sMEC.[Country] AS [Country]
	   ,sMEC.[E-Mail] AS [Email]
	   ,sMEC.[Date of Birth] AS [DateOfBirth]
	   ,CASE
			WHEN sMEC.[Gender] = 0 THEN 'No gender'
			WHEN sMEC.[Gender] = 1 THEN 'Male'
			WHEN sMEC.[Gender] = 2 THEN 'Female'
			ELSE 'No gender'
		END AS [Gender]
	   ,CASE
			WHEN sMEC.[Marital Status] = 0 THEN 'No Marital Status'
			WHEN sMEC.[Marital Status] = 1 THEN 'Single'
			WHEN sMEC.[Marital Status] = 2 THEN 'Married'
			WHEN sMEC.[Marital Status] = 3 THEN 'Divorced'
			WHEN sMEC.[Marital Status] = 4 THEN 'Widowed'
			ELSE 'No Marital Status'
		END AS [MaritalStatus]
	   ,sMEC.[Salutation Code]
	FROM [stg$Member Contact] sMEC
	RIGHT JOIN tMaxRowIDMemberContact tMCO
		ON sMEC.[CompanyPrefix] = tMCO.[CompanyPrefix]
		AND sMEC.[bigint_timestamp] = tMCO.[RowID]
		AND sMEC.[Contact No_] = tMCO.[Contact No_]),

tMemberClub
AS
(SELECT
		sMCL.[CompanyPrefix]
	   ,sMCL.[Description]
	   ,sMCL.[Code]
	FROM [stg$Member Club] sMCL
	RIGHT JOIN tMaxRowIDMemberClub tMCL
		ON sMCL.[CompanyPrefix] = tMCL.[CompanyPrefix]
		AND sMCL.[Code] = tMCL.[Code]
		AND sMCL.[bigint_timestamp] = tMCL.[RowID]),

tMemberScheme
AS
(SELECT
		sMES.[CompanyPrefix]
	   ,sMES.[Description]
	   ,sMES.[Code]
	FROM [stg$Member Scheme] sMES
	RIGHT JOIN tMaxRowIDMemberScheme tMSC
		ON sMES.[CompanyPrefix] = tMSC.[CompanyPrefix]
		AND sMES.[bigint_timestamp] = tMSC.[RowID]
		AND sMES.[Code] = tMSC.[Code]),

tMembers
AS
(SELECT
		tMSC.[CompanyPrefix]
	   ,tMSC.[Card No_] AS [CardNo]
	   ,tMEC.[Account No_] AS [AccountNo]
	   ,tMSC.[Contact No_] AS [ContactNo]
	   ,tMSC.[Status]
	   ,CASE
			WHEN tMSC.[Status] = 0 THEN 'Free'
			WHEN tMSC.[Status] = 1 THEN 'Allocated'
			WHEN tMSC.[Status] = 2 THEN 'Active'
			WHEN tMSC.[Status] = 3 THEN 'Blocked'
			ELSE 'N/A'
		END AS [StatusDescription]
	   ,tMCL.[Description] AS [ClubDescription]
	   ,tMES.[Description] AS [SchemeDescription]
	   ,tMEC.[Fullname]
	   ,tMEC.[FirstName]
	   ,tMEC.[MiddleName]
	   ,tMEC.[Surname]
	   ,tMEC.[Address]
	   ,tMEC.[Address2]
	   ,tMEC.[City]
	   ,tMEC.[PostCode]
	   ,tMEC.[County]
	   ,tMEC.[Country]
	   ,tMEC.[Email]
	   ,tMEC.[DateOfBirth]
	   ,CASE
			WHEN tMEC.[DateOfBirth] IS NULL THEN -1
			WHEN DATEADD(YEAR, DATEDIFF(YEAR,
				tMEC.[DateOfBirth], GETDATE()), tMEC.[DateOfBirth]) > GETDATE() THEN DATEDIFF(YEAR, tMEC.[DateOfBirth], GETDATE()) - 1
			ELSE DATEDIFF(YEAR, tMEC.[DateOfBirth], GETDATE())
		END AS [Age]
	   ,tMEC.[Gender]
	   ,tMEC.[MaritalStatus]
	   ,tMEC.[Salutation Code] AS [SalutationCode]
	   ,tMSC.[First Date Used] AS [FirstDateUsed]
	   ,ISNULL
		((SELECT TOP (1)
				CAST(Date AS DATE) AS Expr1
			FROM [stg$Member Sales Entry] AS sMSE
			WHERE (sMSE.[Member Contact No_] = tMEC.[Contact No_])
			AND (sMSE.CompanyPrefix = tMEC.CompanyPrefix)
			ORDER BY Date DESC)
		, '2000-01-01') AS [LastPurchaseDate]
	   ,CAST(ISNULL
		((SELECT TOP (1)
				DATEDIFF(dd, Date, GETDATE()) AS Expr1
			FROM [stg$Member Sales Entry] AS sMSE
			WHERE (sMSE.[Member Contact No_] = tMEC.[Contact No_])
			AND (sMSE.CompanyPrefix = tMEC.[CompanyPrefix])
			ORDER BY Date DESC)
		, 999) AS INT) AS [DaysSinceLastPurchase]
	   ,MAX(tMSC.[RowID]) AS [RowID]
	   ,GETDATE() AS [BatchDate]
	FROM [tMembershipCard] tMSC
	LEFT OUTER JOIN [tMemberContact] tMEC
		ON tMSC.[CompanyPrefix] = tMEC.[CompanyPrefix]
		AND tMSC.[Contact No_] = tMEC.[Contact No_]
	LEFT OUTER JOIN [tMemberClub] tMCL
		ON tMSC.[CompanyPrefix] = tMCL.[CompanyPrefix]
		AND tMSC.[Club Code] = tMCL.[Code]
	LEFT OUTER JOIN [tMemberScheme] tMES
		ON tMSC.[CompanyPrefix] = tMES.[CompanyPrefix]
		AND tMSC.[Scheme Code] = tMES.[Code]
	GROUP BY tMSC.[CompanyPrefix]
			,tMEC.[CompanyPrefix]
			,tMSC.[Card No_]
			,tMEC.[Account No_]
			,tMSC.[Contact No_]
			,tMEC.[Contact No_]
			,tMSC.[Status]
			,tMCL.[Description]
			,tMES.[Description]
			,tMEC.[Fullname]
			,tMEC.[FirstName]
			,tMEC.[MiddleName]
			,tMEC.[Surname]
			,tMEC.[Address]
			,tMEC.[Address2]
			,tMEC.[City]
			,tMEC.[PostCode]
			,tMEC.[County]
			,tMEC.[Country]
			,tMEC.[EMail]
			,tMEC.[DateofBirth]
			,tMEC.[Gender]
			,tMEC.[MaritalStatus]
			,tMEC.[Salutation Code]
			,tMSC.[First Date Used])


MERGE [DW].[dMember] AS Target USING (SELECT
		COALESCE(dCOM.[SK_Company], -1) AS [Company]
	   ,tMEM.[CardNo]
	   ,tMEM.[AccountNo]
	   ,tMEM.[ContactNo]
	   ,tMEM.[Status]
	   ,tMEM.StatusDescription
	   ,tMEM.ClubDescription
	   ,tMEM.SchemeDescription
	   ,tMEM.[FullName]
	   ,tMEM.[FirstName]
	   ,tMEM.[MiddleName]
	   ,tMEM.[Surname]
	   ,tMEM.[Address]
	   ,tMEM.[Address2]
	   ,tMEM.[City]
	   ,tMEM.[PostCode]
	   ,tMEM.[County]
	   ,tMEM.[Country]
	   ,tMEM.[Email]
	   ,tMEM.[DateOfBirth]
	   ,tMEM.[Age]
	   ,CASE
			WHEN tMEM.[Age] BETWEEN 0 AND 19 THEN '<20'
			WHEN tMEM.[Age] BETWEEN 20 AND 29 THEN '20 - 29'
			WHEN tMEM.[Age] BETWEEN 30 AND 39 THEN '30 - 39'
			WHEN tMEM.[Age] BETWEEN 40 AND 49 THEN '40 - 49'
			WHEN tMEM.[Age] BETWEEN 50 AND 59 THEN '50 - 59'
			WHEN tMEM.[Age] BETWEEN 60 AND 69 THEN '60 - 69'
			WHEN tMEM.[Age] BETWEEN 70 AND 79 THEN '70 - 79'
			WHEN tMEM.[Age] BETWEEN 80 AND 89 THEN '80 - 89'
			WHEN tMEM.[Age] >= 90 THEN '>= 90'
			ELSE NULL
		END AS AgeBanding
	   ,tMEM.[Gender]
	   ,tMEM.[MaritalStatus]
	   ,tMEM.[SalutationCode]
	   ,tMEM.[FirstDateUsed]
	   ,tMEM.[LastPurchaseDate]
	   ,tMEM.[DaysSinceLastPurchase]
	   ,tMEM.[RowID]
	   ,tMEM.[BatchDate]
	FROM [tMembers] tMEM
	LEFT JOIN [DW].[dCompany] dCOM
		ON tMEM.[CompanyPrefix] = dCOM.[CompanyPrefix]) AS Source

ON TARGET.[Company] = Source.[Company]
	AND TARGET.[CardNo] = Source.[CardNo]
	AND TARGET.[ContactNo] = Source.[ContactNo]

WHEN MATCHED
	THEN UPDATE
		SET [Company] = Source.[Company]
		   ,[CardNo] = Source.[CardNo]
		   ,[ContactNo] = Source.[ContactNo]
		   ,[AccountNo] = Source.[AccountNo]
		   ,[CardStatus] = Source.[Status]
		   ,[CardStatusDescription] = Source.[StatusDescription]
		   ,[ClubDescription] = Source.[ClubDescription]
		   ,[SchemeDescription] = Source.[SchemeDescription]
		   ,[FullName] = Source.[FullName]
		   ,[FirstName] = Source.[FirstName]
		   ,[MiddleName] = Source.[MiddleName]
		   ,[Surname] = Source.[Surname]
		   ,[Address] = Source.[Address]
		   ,[Address2] = Source.[Address2]
		   ,[City] = Source.[City]
		   ,[PostCode] = Source.[PostCode]
		   ,[County] = Source.[County]
		   ,[Country] = Source.[Country]
		   ,[Email] = Source.[Email]
		   ,[DateOfBirth] = Source.[DateOfBirth]
		   ,[Age] = Source.[Age]
		   ,[AgeBanding] = Source.[AgeBanding]
		   ,[Gender] = Source.[Gender]
		   ,[MaritalStatus] = Source.[MaritalStatus]
		   ,[SalutationCode] = Source.[SalutationCode]
		   ,[FirstDateUsed] = Source.[FirstDateUsed]
		   ,[LastPurchaseDate] = Source.[LastPurchaseDate]
		   ,[DaysSinceLastPurchase] = Source.[DaysSinceLastPurchase]
		   ,[RowID] = Source.[RowID]
		   ,[BatchDate] = Source.[BatchDate]

WHEN NOT MATCHED BY TARGET
	THEN INSERT ([Company], [CardNo], [ContactNo], [AccountNo], [CardStatus], [CardStatusDescription], [ClubDescription], [SchemeDescription], [FullName], [FirstName], [MiddleName], [Surname], [Address], [Address2], [City], [PostCode], [County], [Country], [Email], [DateOfBirth], [Age], [AgeBanding], [Gender], [MaritalStatus], [SalutationCode], [FirstDateUsed], [LastPurchaseDate], [DaysSinceLastPurchase], [RowID], [BatchDate])
			VALUES (Source.[Company], Source.[CardNo], Source.[ContactNo], Source.[AccountNo], Source.[Status], Source.[StatusDescription], Source.[ClubDescription], Source.[SchemeDescription], Source.[FullName], Source.[FirstName], Source.[MiddleName], Source.[Surname], Source.[Address], Source.[Address2], Source.[City], Source.[PostCode], Source.[County], Source.[Country], Source.[Email], Source.[DateOfBirth], Source.[Age], Source.[AgeBanding], Source.[Gender], Source.[MaritalStatus], Source.[SalutationCode], Source.[FirstDateUsed], Source.[LastPurchaseDate], Source.[DaysSinceLastPurchase], Source.[RowID], Source.[BatchDate])

WHEN NOT MATCHED BY SOURCE
	AND TARGET.[SK_Member] <> -1
	THEN DELETE
;
;
END

SELECT
	'RowCount' = @@rowcount
	
GO

ALTER PROCEDURE [dbo].[v14dimMergedMember] 
@Member [dbo].[MemberType] READONLY

AS 
BEGIN

WITH tMaxRowIDMembershipCard
AS
(SELECT
		sMSC.[CompanyPrefix]
	   ,sMSC.[Card No_] AS CardNo
	   ,sMSC.[Contact No_] AS ContactNo
	   ,MAX(sMSC.[bigint_timestamp]) AS RowID
	FROM [Stg$Membership Card] sMSC
	GROUP BY sMSC.[CompanyPrefix]
			,sMSC.[Card No_]
			,sMSC.[Contact No_]),

tMaxRowIDMemberContact
AS
(SELECT 
		sMCT.[CompanyPrefix]
	   ,sMCT.[Contact No_] 
	   ,MAX(sMCT.[bigint_timestamp]) AS [RowID]
	FROM [Stg$Member Contact] sMCT
	GROUP BY sMCT.[CompanyPrefix]
			,sMCT.[Contact No_]),

tMaxRowIDMemberClub
AS
(SELECT 
		sMCL.[CompanyPrefix]
	   ,sMCL.[Code]
	   ,MAX(sMCL.[bigint_timestamp]) AS [RowID]
	FROM [stg$Member Club] sMCL
	GROUP BY sMCL.[CompanyPrefix]
			,sMCL.[Code]),

tMaxRowIDMemberScheme
AS
(SELECT 
		sMES.[CompanyPrefix]
	   ,sMES.[Code]
	   ,MAX(sMES.[bigint_timestamp]) AS [RowID]
	FROM [stg$Member Scheme] sMES
	GROUP BY sMES.[CompanyPrefix]
			,sMES.[Code]),

tMembershipCard
AS
(SELECT 
	 sMSC.[CompanyPrefix]
	,sMSC.[Contact No_]
	,sMSC.[Account No_]
	,sMSC.[Status]
	,sMSC.[Card No_]
	,sMSC.[First Date Used]
	,sMSC.[Club Code]
	,sMSC.[Scheme Code]
	,sMSC.[bigint_timestamp] AS [RowID]
FROM [stg$Membership Card] sMSC
RIGHT JOIN tMaxRowIDMembershipCard tMRC
	ON sMSC.[CompanyPrefix] = tMRC.[CompanyPrefix]
	AND sMSC.[bigint_timestamp] = tMRC.[RowID]
	AND sMSC.[Card No_] = tMRC.[CardNo]
	AND sMSC.[Contact No_] = tMRC.[ContactNo]),

tMemberContact
AS
(SELECT
	    sMEC.[CompanyPrefix]
	   ,sMEC.[Account No_] 
	   ,sMEC.[Contact No_]
	   ,sMEC.[Name] AS [Fullname]
	   ,sMEC.[First Name] AS [FirstName]
	   ,sMEC.[Middle Name] AS [MiddleName]
	   ,sMEC.[Surname]
	   ,sMEC.[Address]
	   ,sMEC.[Address 2] AS [Address2]
	   ,sMEC.[City]
	   ,sMEC.[Post Code] AS [PostCode]
	   ,sMEC.[County]
	   ,sMEC.[Country_Region Code] AS [Country]
	   ,sMEC.[E-Mail] AS [Email]
	   ,sMEC.[Date of Birth] AS [DateOfBirth]
	   ,CASE
			WHEN sMEC.[Gender] = 0 THEN 'No gender'
			WHEN sMEC.[Gender] = 1 THEN 'Male'
			WHEN sMEC.[Gender] = 2 THEN 'Female'
			ELSE 'No gender'
		END AS [Gender]
	   ,CASE
			WHEN sMEC.[Marital Status] = 0 THEN 'No Marital Status'
			WHEN sMEC.[Marital Status] = 1 THEN 'Single'
			WHEN sMEC.[Marital Status] = 2 THEN 'Married'
			WHEN sMEC.[Marital Status] = 3 THEN 'Divorced'
			WHEN sMEC.[Marital Status] = 4 THEN 'Widowed'
			ELSE 'No Marital Status'
		END AS [MaritalStatus]
	   ,sMEC.[Salutation Code]
	   FROM [stg$Member Contact] sMEC
	   RIGHT JOIN tMaxRowIDMemberContact tMCO
			ON sMEC.[CompanyPrefix] = tMCO.[CompanyPrefix]
			AND sMEC.[bigint_timestamp] = tMCO.[RowID]
			AND sMEC.[Contact No_] = tMCO.[Contact No_]),

tMemberClub
AS
(SELECT 
	 sMCL.[CompanyPrefix]
	,sMCL.[Description]
	,sMCL.[Code]
FROM [stg$Member Club] sMCL
RIGHT JOIN tMaxRowIDMemberClub tMCL
	ON sMCL.[CompanyPrefix] = tMCL.[CompanyPrefix]
	AND sMCL.[Code] = tMCL.[Code]
	AND sMCL.[bigint_timestamp] = tMCL.[RowID]),

tMemberScheme
AS
(SELECT 
	 sMES.[CompanyPrefix]
	,sMES.[Description]
	,sMES.[Code] 
FROM [stg$Member Scheme] sMES
RIGHT JOIN tMaxRowIDMemberScheme tMSC
	ON sMES.[CompanyPrefix] = tMSC.[CompanyPrefix]
	AND sMES.[bigint_timestamp] = tMSC.[RowID]
	AND sMES.[Code] = tMSC.[Code]),

tMembers
AS
(SELECT
		tMSC.[CompanyPrefix]
	   ,tMSC.[Card No_] AS [CardNo]
	   ,tMEC.[Account No_] AS [AccountNo]
	   ,tMSC.[Contact No_] AS [ContactNo]
	   ,tMSC.[Status]
	   ,CASE
			WHEN tMSC.[Status] = 0 THEN 'Free'
			WHEN tMSC.[Status] = 1 THEN 'Allocated'
			WHEN tMSC.[Status] = 2 THEN 'Active'
			WHEN tMSC.[Status] = 3 THEN 'Blocked'
			ELSE 'N/A'
		END AS [StatusDescription]
	   ,tMCL.[Description] AS [ClubDescription]
	   ,tMES.[Description] AS [SchemeDescription]
	   ,tMEC.[Fullname]
	   ,tMEC.[FirstName]
	   ,tMEC.[MiddleName]
	   ,tMEC.[Surname]
	   ,tMEC.[Address]
	   ,tMEC.[Address2]
	   ,tMEC.[City]
	   ,tMEC.[PostCode]
	   ,tMEC.[County]
	   ,tMEC.[Country]
	   ,tMEC.[Email]
	   ,tMEC.[DateOfBirth]
	   ,CASE
			WHEN tMEC.[DateOfBirth] IS NULL THEN -1
			WHEN DATEADD(YEAR, DATEDIFF(YEAR,
				tMEC.[DateOfBirth], GETDATE()), tMEC.[DateOfBirth]) > GETDATE() THEN DATEDIFF(YEAR, tMEC.[DateOfBirth], GETDATE()) - 1
			ELSE DATEDIFF(YEAR, tMEC.[DateOfBirth], GETDATE())
		END AS [Age]
	   ,tMEC.[Gender]
	   ,tMEC.[MaritalStatus]
	   ,tMEC.[Salutation Code] AS [SalutationCode]
	   ,tMSC.[First Date Used] AS [FirstDateUsed]
	   ,ISNULL
		((SELECT TOP (1)
				CAST(Date AS DATE) AS Expr1
			FROM [stg$Member Sales Entry] AS sMSE
			WHERE (sMSE.[Member Contact No_] = tMEC.[Contact No_])
			AND (sMSE.CompanyPrefix = tMEC.CompanyPrefix)
			ORDER BY Date DESC)
		, '2000-01-01') AS [LastPurchaseDate]
	   ,CAST(ISNULL
		((SELECT TOP (1)
				DATEDIFF(dd, Date, GETDATE()) AS Expr1
			FROM [stg$Member Sales Entry] AS sMSE
			WHERE (sMSE.[Member Contact No_] = tMEC.[Contact No_])
			AND (sMSE.CompanyPrefix = tMEC.[CompanyPrefix])
			ORDER BY Date DESC)
		, 999) AS INT) AS [DaysSinceLastPurchase]
	   ,MAX(tMSC.[RowID]) AS [RowID]
	   ,GETDATE() AS [BatchDate]
	FROM [tMembershipCard] tMSC 
	LEFT OUTER JOIN [tMemberContact] tMEC 
		ON tMSC.[CompanyPrefix] = tMEC.[CompanyPrefix]
		AND tMSC.[Contact No_] = tMEC.[Contact No_]
	LEFT OUTER JOIN [tMemberClub] tMCL 
		ON tMSC.[CompanyPrefix] = tMCL.[CompanyPrefix]
		AND tMSC.[Club Code] = tMCL.[Code]
	LEFT OUTER JOIN [tMemberScheme] tMES
		ON tMSC.[CompanyPrefix] = tMES.[CompanyPrefix]
		AND tMSC.[Scheme Code] = tMES.[Code]
	GROUP BY tMSC.[CompanyPrefix]
			,tMEC.[CompanyPrefix]
			,tMSC.[Card No_]
			,tMEC.[Account No_]
			,tMSC.[Contact No_]
			,tMEC.[Contact No_]
			,tMSC.[Status]
			,tMCL.[Description]
			,tMES.[Description]
			,tMEC.[Fullname]
			,tMEC.[FirstName]
			,tMEC.[MiddleName]
			,tMEC.[Surname]
			,tMEC.[Address]
			,tMEC.[Address2]
			,tMEC.[City]
			,tMEC.[PostCode]
			,tMEC.[County]
			,tMEC.[Country]
			,tMEC.[EMail]
			,tMEC.[DateofBirth]
			,tMEC.[Gender]
			,tMEC.[MaritalStatus]
			,tMEC.[Salutation Code]
			,tMSC.[First Date Used])


MERGE [DW].[dMember] AS Target USING 
(SELECT
		COALESCE(dCOM.[SK_Company],-1) AS [Company]
	   ,tMEM.[CardNo]
	   ,tMEM.[AccountNo]
	   ,tMEM.[ContactNo]
	   ,tMEM.[Status]
	   ,tMEM.StatusDescription
	   ,tMEM.ClubDescription
	   ,tMEM.SchemeDescription
	   ,tMEM.[FullName]
	   ,tMEM.[FirstName]
	   ,tMEM.[MiddleName]
	   ,tMEM.[Surname]
	   ,tMEM.[Address]
	   ,tMEM.[Address2]
	   ,tMEM.[City]
	   ,tMEM.[PostCode]
	   ,tMEM.[County]
	   ,tMEM.[Country]
	   ,tMEM.[Email]
	   ,tMEM.[DateOfBirth]
	   ,tMEM.[Age]
	   ,case  
WHEN tMEM.[Age] BETWEEN 0 and 19 then '<20'
WHEN tMEM.[Age] BETWEEN 20 and 29 then '20 - 29'
WHEN tMEM.[Age] BETWEEN 30 and 39 then '30 - 39'
WHEN tMEM.[Age] BETWEEN 40 and 49 then '40 - 49'
WHEN tMEM.[Age] BETWEEN 50 and 59 then '50 - 59'
WHEN tMEM.[Age] BETWEEN 60 and 69 then '60 - 69'
WHEN tMEM.[Age] BETWEEN 70 and 79 then '70 - 79'
WHEN tMEM.[Age] BETWEEN 80 and 89 then '80 - 89'
WHEN tMEM.[Age] >= 90 THEN '>= 90'
ELSE NULL
END AS AgeBanding
	   ,tMEM.[Gender]
	   ,tMEM.[MaritalStatus]
	   ,tMEM.[SalutationCode]
	   ,tMEM.[FirstDateUsed]
	   ,tMEM.[LastPurchaseDate]
	   ,tMEM.[DaysSinceLastPurchase]
	   ,tMEM.[RowID]
	   ,tMEM.[BatchDate]
	FROM [tMembers] tMEM
	LEFT JOIN [DW].[dCompany] dCOM
		ON tMEM.[CompanyPrefix] = dCOM.[CompanyPrefix]
) AS Source

ON TARGET.[Company] = Source.[Company]
	AND TARGET.[CardNo] = Source.[CardNo]
	AND TARGET.[ContactNo] = Source.[ContactNo]

WHEN MATCHED
	THEN UPDATE
		SET [Company] = Source.[Company]
		   ,[CardNo] = Source.[CardNo]
		   ,[ContactNo] = Source.[ContactNo]
		   ,[AccountNo] = Source.[AccountNo]
		   ,[CardStatus] = Source.[Status]
		   ,[CardStatusDescription] = Source.[StatusDescription]
		   ,[ClubDescription] = Source.[ClubDescription]
		   ,[SchemeDescription] = Source.[SchemeDescription]
		   ,[FullName] = Source.[FullName]
		   ,[FirstName] = Source.[FirstName]
		   ,[MiddleName] = Source.[MiddleName]
		   ,[Surname] = Source.[Surname]
		   ,[Address] = Source.[Address]
		   ,[Address2] = Source.[Address2]
		   ,[City] = Source.[City]
		   ,[PostCode] = Source.[PostCode]
		   ,[County] = Source.[County]
		   ,[Country] = Source.[Country]
		   ,[Email] = Source.[Email]
		   ,[DateOfBirth] = Source.[DateOfBirth]
		   ,[Age] = Source.[Age]
		   ,[AgeBanding] = Source.[AgeBanding]
		   ,[Gender] = Source.[Gender]
		   ,[MaritalStatus] = Source.[MaritalStatus]
		   ,[SalutationCode] = Source.[SalutationCode]
		   ,[FirstDateUsed] = Source.[FirstDateUsed]
		   ,[LastPurchaseDate] = Source.[LastPurchaseDate]
		   ,[DaysSinceLastPurchase] = Source.[DaysSinceLastPurchase]
		   ,[RowID] = Source.[RowID]
		   ,[BatchDate] = Source.[BatchDate]

WHEN NOT MATCHED BY TARGET
	THEN INSERT ([Company], [CardNo], [ContactNo], [AccountNo], [CardStatus], [CardStatusDescription], [ClubDescription], [SchemeDescription], [FullName], [FirstName], [MiddleName], [Surname], [Address], [Address2], [City], [PostCode], [County], [Country], [Email], [DateOfBirth], [Age],[AgeBanding], [Gender], [MaritalStatus], [SalutationCode], [FirstDateUsed], [LastPurchaseDate], [DaysSinceLastPurchase], [RowID], [BatchDate])
			VALUES (Source.[Company], Source.[CardNo], Source.[ContactNo], Source.[AccountNo], Source.[Status], Source.[StatusDescription], Source.[ClubDescription], Source.[SchemeDescription], Source.[FullName], Source.[FirstName], Source.[MiddleName], Source.[Surname], Source.[Address], Source.[Address2], Source.[City], Source.[PostCode], Source.[County], Source.[Country], Source.[Email], Source.[DateOfBirth], Source.[Age],Source.[AgeBanding], Source.[Gender], Source.[MaritalStatus], Source.[SalutationCode], Source.[FirstDateUsed], Source.[LastPurchaseDate], Source.[DaysSinceLastPurchase], Source.[RowID], Source.[BatchDate])

WHEN NOT MATCHED BY SOURCE
	AND TARGET.[SK_Member] <> -1
	THEN DELETE
;
;
END

SELECT
	'RowCount' = @@rowcount